import util
import pymysql
from models import User

class UserDao:
    def __init__(self):
        self.con = util.get_connection()

    def add(self,user):
        tu = self.load_by_username(user.username)
        if tu != None:
            print("用户已经存在")
            return None
        try:
            cursor = self.con.cursor()
            sql = "insert into t_user(username,password,nickname,born) value (%s,%s,%s,%s)"
            cursor.execute(sql,(user.username,user.password,user.nickname,user.born))
            self.con.commit()
        except pymysql.MySQLError as e:
            print(e)
            self.con.rollback()
        finally:
            cursor.close()

    def delete(self,id):
        try:
            cursor = self.con.cursor()
            sql = "delete from t_user where id=%s"
            cursor.execute(sql,(id,))
            self.con.commit()
        except pymysql.MySQLError as e:
            print(e)
            self.con.rollback()
        finally:
            cursor.close()

    def update(self,user):
        try:
            with self.con.cursor() as cursor:
                sql = "update t_user set username=%s,password=%s,nickname=%s,born=%s where id=%s"
                cursor.execute(sql,(user.username,user.password,user.nickname,user.born,user.id))
                self.con.commit()
        except pymysql.MySQLError as e:
            print(e)

    def load(self,id):
        try:
            with self.con.cursor() as cursor:
                sql = "select * from t_user where id=%s"
                cursor.execute(sql,(id,))
                user = User(*cursor.fetchone())
                return user
        except pymysql.MySQLError as e:
            print(e)
        return None

    def load_by_username(self,username):
        try:
            with self.con.cursor() as course:
                sql = "select * from t_user where username=%s"
                course.execute(sql,(username,))
                result = course.fetchone()
                print(result)
                if result != None:
                    user = User(*result)
                    return user
        except pymysql.MySQLError as e:
            print(e)
        return None

    def find(self):
        users = []
        try:
            with self.con.cursor() as cursor:
                sql = "select * from t_user"
                cursor.execute(sql)
                for u in cursor.fetchall():
                  users.append(User(*u))
        except pymysql.MySQLError as e:
            print(e)
        return users

    def login(self,username,password):
        user = self.load_by_username(username)
        if user == None:
            print("用户名不存在")
            return None
        if password != user.password:
            print("密码不正确")
            return None
        return user
